﻿using System;
using System.Collections.Generic;
using System.Web;
using NhanAi.Entity;
using Home.Utils;
using System.Data.OleDb;

namespace NhanAi.DAO
{
    public class SubscribingDao:DBConnection
    {
        public void Create(Subscribing t)
        {
             NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmSubscribing");
             try
             {
                 if (Connect())
                 {
                     nb.AddValueField("Subscriber", t.Subscriber);
                     nb.AddValueField("SubscriberEmail", t.SubscriberEmail);
                     nb.AddValueField("SubscribingContent", t.SubscribingContent);
                     nb.AddValueField("SubscribingDate", t.SubscribingDate.ToString());
                     OleDbCommand cmd = nb.GetCreateCommand(conn);

                     cmd.ExecuteNonQuery();
                     cmd.CommandText = "select @@IDENTITY";
                     t.Id = (int)cmd.ExecuteScalar();
                     Disconnect();
                 }
             }
             catch (Exception ex) { throw ex; }
        }
        public void Update(Subscribing t)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmSubscribing");
            try
            {
                if (Connect())
                {             
                    nb.AddValueField("Subscriber", t.Subscriber);
                    nb.AddValueField("SubscriberEmail", t.SubscriberEmail);
                    nb.AddValueField("SubscribingContent", t.SubscribingContent);
                    nb.AddValueField("SubscribingDate", t.SubscribingDate.ToString());
                    nb.AddConditionField("Id", t.Id);
                    OleDbCommand cmd = nb.GetUpdateCommand(conn,true);
                    cmd.ExecuteNonQuery();                
                    
                    Disconnect();
                }
            }
            catch (Exception ex) { throw ex; }
        }
        public void Delete(int id)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmSubscribing");
            try
            {
                if (Connect())
                {                   
                    nb.AddConditionField("Id", id);
                    OleDbCommand cmd = nb.GetUpdateCommand(conn,true);
                    cmd.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (Exception ex) { throw ex; }
        }
        public void Delete(string ids)
        {          
            OleDbCommand cmd = new OleDbCommand("delete from HmSubscribing where Id in (" + ids + ")", conn);
            if (Connect())
            {
                cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        public Subscribing Read(int id)
        {
            Subscribing t = null;
            if (Connect())
            {
                OleDbCommand cmd = new OleDbCommand("select * from HmSubscribing where ID=@id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    t = new Subscribing();
                    t.Id = dr.GetInt32(0);
                    t.Subscriber = dr.GetString(1);
                    t.SubscriberEmail = dr.GetString(2);
                    t.SubscribingContent = dr.GetString(3);
                    t.SubscribingDate = dr.GetDateTime(4);       
                }
                Disconnect();
            }
            return t;
        }
        public List<Subscribing> ReadMany(int pageSize, int pageNum, out int total)
        {
            List<Subscribing> list = new List<Subscribing>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmSubscribing", conn);
            total = 0;
            if (Connect())
            {
                total = (int)cmd.ExecuteScalar();
                QueryCommandBuilder qb = new QueryCommandBuilder("select TOP #pageSize * from (select TOP #total * from HmSubscribing where 1=1");
                qb.AddSuffix("order by Id desc) order by Id asc");
                qb.AddParam("pageSize", pageSize);
                int tmp = total - (pageNum - 1) * pageSize;
                qb.AddParam("total", tmp > 0 ? tmp : 1);                

                cmd = qb.GetBuildedCommand(conn);

                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                  Subscribing  t = new Subscribing();
                    t.Id = dr.GetInt32(0);
                    t.Subscriber = dr.GetString(1);
                    t.SubscriberEmail = dr.GetString(2);
                    t.SubscribingContent = dr.GetString(3);
                    t.SubscribingDate = dr.GetDateTime(4);    
                   list.Add(t);
                }
            }

            return list;
        }
    }
}